package com.ruoyi.common.sdk.connection;

import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.sdk.oos.AliyunOosClient;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import org.apache.logging.log4j.util.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;

public class MySqlConnectionClient {

    private static final Logger log = LoggerFactory.getLogger(MySqlConnectionClient.class);
    /** 数据库IP */
    private String mysqlIp;
    /** 数据库端口 */
    private String mysqlPort;
    /** 数据库名称 */
    private String databaseName;
    /** 数据库用户名 */
    private String databaseUsername;
    /** 数据库密码 */
    private String databasePassword;

    private MySqlConnectionClient(Builder builder){
        this.mysqlIp=builder.mysqlIp;
        this.mysqlPort=builder.mysqlPort;
        this.databaseName=builder.databaseName;
        this.databaseUsername=builder.databaseUsername;
        this.databasePassword=builder.databasePassword;
    }

    // 内部静态Builder类
    public static class Builder {
        /** 数据库IP */
        private String mysqlIp;

        /** 数据库端口 */
        private String mysqlPort;
        /** 数据库名称 */
        private String databaseName;
        /** 数据库用户名 */
        private String databaseUsername;
        /** 数据库密码 */
        private String databasePassword;

        public Builder() {
        }

        public Builder mySqlIp(String mysqlIp) {
            this.mysqlIp = mysqlIp;
            return this;
        }
        public Builder mySqlPort(String mysqlPort) {
            this.mysqlPort = mysqlPort;
            return this;
        }
        public Builder databaseName(String databaseName) {
            this.databaseName = databaseName;
            return this;
        }
        public Builder databaseUsername(String databaseUsername) {
            this.databaseUsername = databaseUsername;
            return this;
        }
        public Builder databasePassword(String databasePassword) {
            this.databasePassword = databasePassword;
            return this;
        }
        public MySqlConnectionClient build() {
            return new MySqlConnectionClient(this);
        }

    }






    public Connection getClition() throws SQLException {
        //2.获取数据库连接对象（这里使用的数据库是jdbcstudy）
        log.info("----------开始链接数据库----------");
        String url="jdbc:mysql://"+mysqlIp+":"+mysqlPort+"/"+databaseName+"?useUnicode=true&characterEncoding=utf8&useSSL=true";
        log.info("连接MySQL---URL："+url);
        log.info("连接MySQL---UserName："+databaseUsername);
        log.info("连接MySQL---Password："+databasePassword);
        return DriverManager.getConnection(url,databaseUsername,databasePassword);
        //mysql8需要加上时区
    }


    /**
     * 备份数据
     */
    public String backupDB() {

        Connection conn = null;
        String dbName = null;
        String filePath="";
        try {
            conn = getClition();
            dbName = conn.getCatalog();
        } catch (SQLException e) {
            log.info("无法获取数据库连接！");
            throw new RuntimeException("无法获取数据库连接！");
        }
        String tableName = null,
                procName = null;
        BufferedWriter writer = null;
        Statement stmtInfo = null, stmtData = null;
        ResultSet rsInfo = null, rsData = null;
        try {
            //存放文件目录
//            String folderBackup = CommonUtils.absoluteClassPathUsrPath(Constants.FOLDER_BACKUP);
            //文件名
            String dateTimeNow = DateUtils.dateTimeNow();
            String sqlFilename = String.format("%s-%s.sql", dbName,dateTimeNow);
            filePath="./change/" + sqlFilename;

            // 使用 Paths.get() 方法转换为 Path 对象
            Path path = Paths.get("./change/");
            // 使用 Files.exists() 方法检查路径是否存在
            if (!Files.exists(path)) {
                // 如果路径不存在，尝试创建目录
                try {
                    Files.createDirectories(path);
                    log.debug("目录创建成功: " + path);
                } catch (IOException e) {
                    log.debug("创建目录失败: " + e.getMessage());
                }
            } else {
                log.debug("目录已存在: " + path);
            }
            writer = new BufferedWriter(new FileWriter(filePath));

            /*
             * 头内容
             */
            writer.write("/*!40101 SET NAMES utf8 */;");
            writer.newLine();
            writer.write("/*!40101 SET SQL_MODE=''*/;");
            writer.newLine();
            writer.write("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;");
            writer.newLine();
            writer.write("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;");
            writer.newLine();
            writer.write("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;");
            writer.newLine();
            writer.write("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;");
            log.info("----------开始备份数据----------");
            writer.flush();

            /*
             * 导出表数据
             */
            stmtInfo = conn.createStatement();
            rsInfo = stmtInfo.executeQuery(String.format("SHOW FULL TABLES FROM `%s` WHERE TABLE_TYPE = 'BASE TABLE'", dbName));
            // 遍历所有表
            while (rsInfo.next()) {
                tableName = rsInfo.getString(1);
                this.dbBackExportTable(conn, tableName, writer, false);
            }    //end for tables
            if (null != rsInfo) {
                rsInfo.close();
            }
            if (null != stmtInfo) {
                stmtInfo.close();
            }

            /*
             * 导出存储过程信息
             */
            stmtInfo = conn.createStatement();
            rsInfo = stmtInfo.executeQuery(String.format("SELECT `SPECIFIC_NAME` from `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = '%s' AND ROUTINE_TYPE = 'PROCEDURE'; ", dbName));
            while (rsInfo.next()) {
                procName = rsInfo.getString(1);

                /* 存储过程结构 */
                stmtData = conn.createStatement();
                rsData = stmtData.executeQuery(String.format("SHOW CREATE PROCEDURE `%s`", procName));
                if (!rsData.next()) {
                    continue;
                }
                writer.newLine();
                writer.newLine();
                writer.write(String.format("/* Procedure structure for procedure `%s` */", procName));
                writer.newLine();
                writer.write(String.format("/*!50003 DROP PROCEDURE IF EXISTS  `%s` */;", procName));
                writer.newLine();
                writer.write("DELIMITER $$");
                writer.newLine();
                writer.append("/*!50003 ").append(rsData.getString(3)).append(" */$$");
                writer.newLine();
                writer.write("DELIMITER ;");

                if (null != rsData) {
                    rsData.close();
                }
                if (null != stmtData) {
                    stmtData.close();
                }
            }

            /*
             * 尾内容
             */
            writer.newLine();
            writer.newLine();
            writer.write("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;");
            writer.newLine();
            writer.write("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;");
            writer.newLine();
            writer.write("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;");
            writer.newLine();
            writer.write("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;");
            writer.newLine();

            writer.close();
            if (null != stmtData) {
                stmtData.close();
            }
            if (null != rsData) {
                rsData.close();
            }
            if (null != stmtInfo) {
                stmtInfo.close();
            }
            if (null != rsInfo) {
                rsInfo.close();
            }
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        return filePath;
    }


    /**
     * 导出表数据
     * @param conn
     * @param tableName
     * @param writer
     * @param bulkFlag   是否将数据放在一起
     * @throws SQLException
     * @throws IOException
     */
    private void dbBackExportTable(Connection conn, String tableName, BufferedWriter writer, boolean bulkFlag) throws SQLException, IOException {
        log.info("----------导出表:"+tableName+"----------");
        Statement stmt = null;
        ResultSet rs = null;
        /* 表结构 */
        stmt = conn.createStatement();
        rs = stmt.executeQuery(String.format("SHOW CREATE TABLE `%s`", tableName));
        if(!rs.next()) {
            return ;
        }
        writer.newLine();
        writer.newLine();
        writer.write(String.format("/*Table structure for table `%s` */", tableName));
        writer.newLine();
        writer.write(String.format("DROP TABLE IF EXISTS `%s`;", tableName));
        writer.newLine();
        writer.write(rs.getString(2) + ";");
        writer.newLine();
        if(null != rs) {
            rs.close();
        }
        if(null != stmt) {
            stmt.close();
        }

        /* 导出表数据 */
        // 先获取记录数
        stmt = conn.createStatement();
        rs = stmt.executeQuery(String.format("SELECT COUNT(1) FROM `%s`", tableName));
        int rowCount = rs.next() ? rs.getInt(1) : 0;
        if(0 >= rowCount) {
            writer.flush();
            return ;
        }
        writer.write(String.format("/*Data for the table `%s` */", tableName));
        writer.newLine();

        stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        stmt.setFetchDirection(ResultSet.FETCH_REVERSE);
        rs = stmt.executeQuery(String.format("SELECT * FROM `%s`", tableName));
        int colCount = 0;
        Object colValue = null;
        // 所有数据用","连接
        if(!bulkFlag) {
            while(rs.next()) {
                colCount = rs.getMetaData().getColumnCount();

                writer.write(String.format("INSERT INTO `%s` VALUES (", tableName));
                // 获取表每一列数据
                for(int j = 0; j < colCount; j ++) {
                    if(j > 0) {
                        writer.write(',');
                    }
                    colValue = rs.getObject(j + 1);
                    if(null != colValue) {
                        writer.write(String.format("'%s'", escapeString(colValue.toString())));
                    } else {
                        writer.write("NULL");
                    }
                }    //end for one record columns
                writer.write(");");
                writer.newLine();
                writer.flush();
            }    //end for table records
        }
        // 每行数据独立分开
        else {
            ResultSetMetaData rsMetaData = null;
            int counter = 0;
            while(rs.next()) {
                ++ counter;
                rsMetaData = rs.getMetaData();
                colCount = rsMetaData.getColumnCount();

                // 第一条记录，则列出列名
                if(1 == counter) {
                    writer.write(String.format("INSERT INTO `%s` (", tableName));
                    for(int i = 0; i < colCount; i ++) {
                        if(i > 0) {
                            writer.write(",");
                        }
                        writer.append('`').append(rsMetaData.getColumnName(i + 1)).append('`');
                    }
                    writer.append(") VALUES ");
                }
                // 获取表每一列数据
                for(int j = 0; j < colCount; j ++) {
                    writer.write((0 >= j) ? '(' : ',');
                    colValue = rs.getObject(j + 1);
                    if(null != colValue) {
                        writer.write(String.format("'%s'", escapeString(colValue.toString())));
                    } else {
                        writer.write("NULL");
                    }
                }    //end for one record columns
                // 是否是最后记录
                if(rowCount > counter) {
                    writer.write("),");
                } else {
                    writer.write(");");
                }
                writer.flush();
            }    //end for table records
        }

        if(null != rs) {
            rs.close();
        }
        if(null != stmt) {
            stmt.close();
        }
    }




    /**
     * 判断内容是否需要进行转义
     * @param x
     * @return
     */
    private  boolean isNeedEscape(String x) {
        boolean needsHexEscape = false;
        if(StringUtils.isBlank(x)) {
            return needsHexEscape;
        }
        int stringLength = x.length();
        int i = 0;
        do
        {
            if(i >= stringLength)
                break;
            char c = x.charAt(i);
            switch(c)
            {
                case 0: // '\0'
                    needsHexEscape = true;
                    break;

                case 10: // '\n'
                    needsHexEscape = true;
                    break;

                case 13: // '\r'
                    needsHexEscape = true;
                    break;

                case 92: // '\\'
                    needsHexEscape = true;
                    break;

                case 39: // '\''
                    needsHexEscape = true;
                    break;

                case 34: // '"'
                    needsHexEscape = true;
                    break;

                case 26: // '\032'
                    needsHexEscape = true;
                    break;
            }
            if(needsHexEscape)
                break;
            i++;
        } while(true);
        return needsHexEscape;
    }


    /**
     * 对mysql字符进行转义
     * @param x
     * @return
     */
    private  String escapeString(String x) {
        if(Strings.isBlank(x)) {
            return x;
        }
        if(!isNeedEscape(x)) {
            return x;
        }
        int stringLength = x.length();
        String parameterAsString = x;
        StringBuffer buf = new StringBuffer((int)((double)x.length() * 1.1000000000000001D));
        // 可以指定结果前后追加单引号：'
        //buf.append('\'');
        for(int i = 0; i < stringLength; i++)
        {
            char c = x.charAt(i);
            switch(c)
            {
                default:
                    break;

                case 0: // '\0'
                    buf.append('\\');
                    buf.append('0');
                    continue;

                case 10: // '\n'
                    buf.append('\\');
                    buf.append('n');
                    continue;

                case 13: // '\r'
                    buf.append('\\');
                    buf.append('r');
                    continue;

                case 92: // '\\'
                    buf.append('\\');
                    buf.append('\\');
                    continue;

                case 39: // '\''
                    buf.append('\\');
                    buf.append('\'');
                    continue;

                case 34: // '"'
                    buf.append('\\');
                    buf.append('"');
                    continue;

                case 26: // '\032'
                    buf.append('\\');
                    buf.append('Z');
                    continue;

            }
            buf.append(c);
        }
        // 可以指定结果前后追加单引号：'
        //buf.append('\'');
        parameterAsString = buf.toString();
        return parameterAsString;
    }

}
